package com.dq.repository.message;

import com.dq.domain.message.oneself.Message;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author cf
 */
public interface MessageRepository extends JpaRepository<Message, Long> {

    Page<Message> findByCreateTimeGreaterThanEqualAndCreateTimeLessThanEqualOrderByCreateTimeDesc(Long beginTime, Long endTime, Pageable pageable);
    Long countByCreateTimeGreaterThanEqualAndCreateTimeLessThanEqual(Long beginTime,Long endTime);


    @Query(value = "SELECT m FROM Message m LEFT outer JOIN InstructionStaffuserCentre i " +
            "LEFT outer JOIN ApprovalUserCentre a WHERE m.deleted = false and (m.caseUser.id = :userId OR i.staffUser.id = :userId OR a.caseUser.id = :userId)")
    List<Message> messageList(Long userId);


    @Query(value = "SELECT m.* FROM message m " +
            "LEFT JOIN instruction_staffuser_centre i ON i.instruction_message_id=m.id " +
            "LEFT JOIN approval_user_centre a ON a.approval_message_id=m.id " +
            "LEFT JOIN subscribe s ON s.id=m.subscribe_id " +
            "where m.deleted = false " +
            "and if(:beginSearchTime is not null, m.create_time >= :beginSearchTime, 1=1) " +
            "and if(:endSearchTime is not null, m.create_time < :endSearchTime, 1=1) " +
            "and m.message_type in :messageTypes " +
            "and if(:theme is not null, m.theme like :theme, 1=1) " +
            "and if(" +
                    ":searchType = 'boss', " +
                    "((m.create_user_id = :userId and m.dtype = 'NoticeMessage') OR (m.create_user_id = :userId and m.dtype = 'InstructionMessage') OR a.case_user_id = :userId), " +
                    "(" +
                        "if(" +
                            ":searchType = 'org', " +
                            "((m.case_user_id = :userId and m.dtype = 'NoticeMessage') OR (m.org_id = :orgId and m.user_type = 0 and m.dtype = 'SubscribeMessage') OR i.staff_user_id = :userId OR (m.create_user_id = :userId and m.dtype = 'ApprovalMessage')), " +
                            "((m.case_user_id = :userId and m.dtype = 'NoticeMessage') OR (m.case_user_id = :userId and m.user_type = 0 and m.dtype = 'SubscribeMessage') OR i.staff_user_id = :userId OR (m.create_user_id = :userId and m.dtype = 'ApprovalMessage')) " +
                        ") " +
                    ") " +
                ") " +
            " limit :page, :pagesize"
            , nativeQuery = true)
    List<Message> messageListSql(String searchType, String theme, List messageTypes, Long userId, Long orgId, Long beginSearchTime, Long endSearchTime, Integer page, Integer pagesize);

    @Query(value = "SELECT count(m.id) FROM message m " +
            "LEFT JOIN instruction_staffuser_centre i ON i.instruction_message_id=m.id " +
            "LEFT JOIN approval_user_centre a ON a.approval_message_id=m.id " +
            "LEFT JOIN subscribe s ON s.id=m.subscribe_id " +
            "where m.deleted = false " +
            "and if(:beginSearchTime is not null, m.create_time >= :beginSearchTime, 1=1) " +
            "and if(:endSearchTime is not null, m.create_time < :endSearchTime, 1=1) " +
            "and message_type in :messageTypes " +
            "and if(:theme is not null, m.theme like :theme, 1=1) " +
            "and if(" +
            ":searchType = 'boss', " +
            "((m.create_user_id = :userId and m.dtype = 'NoticeMessage') OR (m.create_user_id = :userId and m.dtype = 'InstructionMessage') OR a.case_user_id = :userId), " +
            "(" +
            "if(" +
            ":searchType = 'org', " +
            "((m.case_user_id = :userId and m.dtype = 'NoticeMessage') OR (m.org_id = :orgId and m.user_type = 0 and m.dtype = 'SubscribeMessage') OR i.staff_user_id = :userId OR (m.create_user_id = :userId and m.dtype = 'ApprovalMessage')), " +
            "((m.case_user_id = :userId and m.dtype = 'NoticeMessage') OR (m.case_user_id = :userId and m.user_type = 0 and m.dtype = 'SubscribeMessage') OR i.staff_user_id = :userId OR (m.create_user_id = :userId and m.dtype = 'ApprovalMessage')) " +
            ") " +
            ") " +
            ") "
            , nativeQuery = true)
    Long messageCountSql(String searchType, String theme, List messageTypes, Long userId, Long orgId, Long beginSearchTime, Long endSearchTime);

}
